package com.rolerealm.common.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.event.EventListener;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.boot.context.event.ApplicationReadyEvent;

/**
 * 轻量级数据库校验/修复：为旧库补齐缺失列，避免 SQL 语法错误。
 * 注意：仅做幂等的结构补丁，不会破坏已有数据。
 */
@Slf4j
@Component
public class SchemaMigrationRunner {

    @Autowired(required = false)
    private JdbcTemplate jdbcTemplate;

    @Value("${spring.datasource.url:}")
    private String datasourceUrl;

    @EventListener(ApplicationReadyEvent.class)
    public void applyMigrations() {
        if (jdbcTemplate == null) return;
        try {
            String dbName = extractDatabaseName(datasourceUrl);
            if (dbName == null) return;

            // characters.deleted 列
            ensureColumnExists(dbName, "characters", "deleted",
                    "ALTER TABLE `characters` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除(0:未删除 1:已删除)' AFTER `updated_at`"
            );

            // skills.deleted 列
            ensureColumnExists(dbName, "skills", "deleted",
                    "ALTER TABLE `skills` ADD COLUMN `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除(0:未删除 1:已删除)' AFTER `updated_at`"
            );

            // 视图依赖 deleted，尝试重建（忽略失败）
            recreateViewsIfPresent();
        } catch (Exception e) {
            log.warn("Schema migration skipped: {}", e.getMessage());
        }
    }

    private void ensureColumnExists(String dbName, String table, String column, String alterSql) {
        Integer cnt = jdbcTemplate.queryForObject(
                "SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND COLUMN_NAME=?",
                Integer.class, dbName, table, column);
        if (cnt == null || cnt == 0) {
            log.info("[DB MIGRATE] Add missing column {}.{}", table, column);
            try { jdbcTemplate.execute(alterSql); } catch (Exception e) { log.warn("Add column failed: {}", e.getMessage()); }
        }
    }

    private void recreateViewsIfPresent() {
        try { jdbcTemplate.execute("DROP VIEW IF EXISTS `character_stats`"); } catch (Exception ignore) {}
        try { jdbcTemplate.execute("DROP VIEW IF EXISTS `user_behavior_stats`"); } catch (Exception ignore) {}
        try {
            jdbcTemplate.execute(
                    "CREATE VIEW `character_stats` AS SELECT c.id, c.name, c.category, c.skill_type, " +
                            "COUNT(ui.id) as interaction_count, " +
                            "COUNT(CASE WHEN ui.interaction_type = 'chat' THEN 1 END) as chat_count, " +
                            "COUNT(CASE WHEN ui.interaction_type = 'favorite' THEN 1 END) as favorite_count, " +
                            "AVG(ui.duration_seconds) as avg_duration " +
                            "FROM characters c LEFT JOIN user_interactions ui ON c.id = ui.character_id " +
                            "WHERE c.deleted = 0 AND c.status = 1 " +
                            "GROUP BY c.id, c.name, c.category, c.skill_type"
            );
        } catch (Exception ignore) {}
        try {
            jdbcTemplate.execute(
                    "CREATE VIEW `user_behavior_stats` AS SELECT ui.user_id, ui.character_id, c.name as character_name, c.category, " +
                            "COUNT(ui.id) as total_interactions, " +
                            "COUNT(CASE WHEN ui.interaction_type = 'chat' THEN 1 END) as chat_count, " +
                            "COUNT(CASE WHEN ui.interaction_type = 'favorite' THEN 1 END) as favorite_count, " +
                            "SUM(ui.duration_seconds) as total_duration, MAX(ui.created_at) as last_interaction " +
                            "FROM user_interactions ui JOIN characters c ON ui.character_id = c.id " +
                            "WHERE c.deleted = 0 AND c.status = 1 " +
                            "GROUP BY ui.user_id, ui.character_id, c.name, c.category"
            );
        } catch (Exception ignore) {}
    }

    private static String extractDatabaseName(String url) {
        if (url == null) return null;
        int q = url.indexOf('?');
        String base = q > 0 ? url.substring(0, q) : url;
        int slash = base.lastIndexOf('/');
        return slash > 0 ? base.substring(slash + 1) : null;
    }
}


